package com.ljj.examisto.util

import android.annotation.SuppressLint
import android.content.ContentValues
import android.content.Context
import android.content.Intent
import android.database.Cursor
import android.database.SQLException
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.util.Log
import androidx.localbroadcastmanager.content.LocalBroadcastManager
import com.ljj.examisto.data.item.Reminder
import com.ljj.examisto.util.Logger.TAG
import java.text.SimpleDateFormat
import java.util.Calendar
import java.util.Date
import java.util.Locale

class DatabaseHelper private constructor(private val context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
    companion object {
        const val DATABASE_NAME = "examisto.db"
        const val DATABASE_VERSION = 7

        @SuppressLint("StaticFieldLeak")
        @Volatile
        private var INSTANCE: DatabaseHelper? = null
        fun getInstance(context: Context): DatabaseHelper {
            return INSTANCE ?: synchronized(this) {
                INSTANCE ?: DatabaseHelper(context.applicationContext).also { INSTANCE = it }
            }
        }
    }

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(ExamContract.SQL_CREATE_TABLE)
        db.execSQL(ReminderContract.SQL_CREATE_TABLE)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        Log.d(TAG, "数据库触发onUpgrade")
        if (oldVersion < newVersion) {
            db.execSQL("DROP TABLE IF EXISTS ${ExamContract.TABLE_NAME}")
            db.execSQL("DROP TABLE IF EXISTS ${ReminderContract.TABLE_NAME}")
            onCreate(db)
        }
    }

    /* CRUD函数 */
    fun insertExam(examName: String, location: String, startTime: Long, duration: Int, remark: String): Boolean {
        return writableDatabase.use { db ->
            try {
                val endTime = calculateEndTime(startTime, duration)
                val values = ContentValues().apply {
                    put(ExamContract.COLUMN_EXAM_NAME, examName)
                    put(ExamContract.COLUMN_LOCATION, location)
                    put(ExamContract.COLUMN_START_TIME, startTime)
                    put(ExamContract.COLUMN_DURATION, duration)
                    put(ExamContract.COLUMN_END_TIME, endTime)
                    put(ExamContract.COLUMN_REMARK, remark)
                    put(ExamContract.COLUMN_SET_TIME, System.currentTimeMillis())
                }
                val result = db.insert(ExamContract.TABLE_NAME, null, values)
                if (result == -1L) {
                    false
                } else {
                    sendExamChangeBroadcast()
                    true
                }
            } catch (e: Exception) {
                Log.e(TAG, "插入考试记录时出错: ${e.message}")
                false
            }
        }
    }

    fun insertReminders(examName: String, startTime: Long, reminders: List<Reminder>): Boolean {
        return writableDatabase.use { db ->
            try {
                db.beginTransaction()
                for (reminder in reminders) {
                    val seconds = reminder.value
                    val reminderTime = calculateReminderTime(startTime, seconds)
                    val type = reminder.type
                    val values = ContentValues().apply {
                        put(ReminderContract.COLUMN_EXAM_NAME, examName)
                        put(ReminderContract.COLUMN_SECONDS, seconds)
                        put(ReminderContract.COLUMN_REMINDER_TIME, reminderTime)
                        put(ReminderContract.COLUMN_TYPE, type.toString())
                    }
                    val result = db.insert(ReminderContract.TABLE_NAME, null, values)
                    if (result == -1L) {
                        Log.e(TAG, "插入提醒记录失败：$reminder")
                        db.endTransaction()
                        return@use false
                    }
                }
                db.setTransactionSuccessful()
                sendReminderChangeBroadcast()
                true
            } catch (e: Exception) {
                Log.e(TAG, "插入提醒记录时出错: ${e.message}")
                false
            } finally {
                db.endTransaction()
            }
        }
    }

    fun updateExam(examName: String, location: String, startTime: Long, duration: Int, remark: String): Boolean {
        return writableDatabase.use { db ->
            try {
                val endTime = calculateEndTime(startTime, duration)
                val values = ContentValues().apply {
                    put(ExamContract.COLUMN_LOCATION, location)
                    put(ExamContract.COLUMN_START_TIME, startTime)
                    put(ExamContract.COLUMN_DURATION, duration)
                    put(ExamContract.COLUMN_END_TIME, endTime)
                    put(ExamContract.COLUMN_REMARK, remark)
                }
                val whereClause = "${ExamContract.COLUMN_EXAM_NAME} = ?"
                val whereArgs = arrayOf(examName)
                val result = db.update(ExamContract.TABLE_NAME, values, whereClause, whereArgs)
                if (result <= 0) {
                    false
                } else {
                    sendExamChangeBroadcast()
                    true
                }
            } catch (e: Exception) {
                Log.e(TAG, "更新考试记录时出错: ${e.message}")
                false
            }
        }
    }

    fun updateReminders(examName: String, startTime: Long, reminders: List<Reminder>): Boolean {
        val db = writableDatabase
        return try {
            db.beginTransaction()
            // 删除现有提醒
            val deletedRows = db.delete(
                ReminderContract.TABLE_NAME,
                "${ReminderContract.COLUMN_EXAM_NAME} = ?",
                arrayOf(examName)
            )
            Log.d(TAG, "已删除 $deletedRows 条旧提醒记录")
            // 批量插入新提醒
            reminders.forEach { reminder ->
                ContentValues().apply {
                    put(ReminderContract.COLUMN_EXAM_NAME, examName)
                    put(ReminderContract.COLUMN_SECONDS, reminder.value)
                    put(ReminderContract.COLUMN_REMINDER_TIME,
                        calculateReminderTime(startTime, reminder.value))
                    put(ReminderContract.COLUMN_TYPE, reminder.type.toString())
                }.let { values ->
                    if (db.insert(ReminderContract.TABLE_NAME, null, values) == -1L) {
                        throw SQLException("插入提醒失败: $reminder")
                    }
                }
            }
            // 标记事务成功并发送广播
            db.setTransactionSuccessful()
            sendReminderChangeBroadcast()
            true
        } catch (e: Exception) {
            Log.e(TAG, "更新提醒失败", e)
            false
        } finally {
            try {
                db.endTransaction()
            } catch (e: Exception) {
                Log.e(TAG, "结束事务时出错", e)
            }
        }
    }

    fun deleteExamWithReminders(examName: String): Boolean {
        return writableDatabase.use { db ->
            try {
                db.beginTransaction()
                val examWhere = "${ExamContract.COLUMN_EXAM_NAME} = ?"
                val reminderWhere = "${ReminderContract.COLUMN_EXAM_NAME} = ?"
                val args = arrayOf(examName)
                db.delete(ExamContract.TABLE_NAME, examWhere, args)
                db.delete(ReminderContract.TABLE_NAME, reminderWhere, args)
                db.setTransactionSuccessful()
                sendExamChangeBroadcast()
                sendReminderChangeBroadcast()
                true
            } catch (e: Exception) {
                Log.e(TAG, "删除考试记录时出错: ${e.message}")
                false
            } finally {
                db.endTransaction()
            }
        }
    }

    fun deleteExpiredExams(): Int {
        val db = this.writableDatabase
        val currentTimeStr = SimpleDateFormat("yyyyMMddHHmm", Locale.getDefault()).format(Date())
        var deleteCount = 0
        try {
            deleteCount = db.delete(ExamContract.TABLE_NAME, "${ExamContract.COLUMN_END_TIME} <= ?", arrayOf(currentTimeStr))
            if (deleteCount > 0) {
                sendExamChangeBroadcast()
            }
            Log.d(TAG, "已删除过期考试数量: $deleteCount")
        } catch (e: Exception) {
            Log.e(TAG, "删除过期考试出错: ${e.message}")
        } finally {
            db.close()
        }
        return deleteCount
    }

    fun getExamNames(): List<String> {
        return readableDatabase.use { db ->
            val examNames = mutableListOf<String>()
            val columns = arrayOf(ExamContract.COLUMN_EXAM_NAME)
            val cursor = db.query(ExamContract.TABLE_NAME, columns, null, null, null, null, null)
            cursor.use {
                if (it.moveToFirst()) {
                    do {
                        val examName = it.getString(it.getColumnIndexOrThrow(ExamContract.COLUMN_EXAM_NAME))
                        examNames.add(examName)
                    } while (it.moveToNext())
                }
            }
            examNames
        }
    }

    fun getExamByName(examName: String): Cursor? {
        return readableDatabase.use { db ->
            val selection = "${ExamContract.COLUMN_EXAM_NAME} = ?"
            val args = arrayOf(examName)
            val cursor = db.query(ExamContract.TABLE_NAME, null, selection, args, null, null, null)
            if (cursor.moveToFirst()) cursor else null
        }
    }


    fun getRemindersByName(examName: String): Cursor? {
        return readableDatabase.use { db ->
            val selection = "${ReminderContract.COLUMN_EXAM_NAME} = ?"
            val args = arrayOf(examName)
            val cursor = db.query(ReminderContract.TABLE_NAME, null, selection, args, null, null, null)
            if (cursor.moveToFirst()) cursor else null
        }
    }

    fun getLatestExam(): Cursor? {
        return readableDatabase.use { db ->
            val cursor = db.query(
                ExamContract.TABLE_NAME,
                null,
                null,
                null,
                null,
                null,
                "${ExamContract.COLUMN_START_TIME} ASC",
                "1"
            )
            if (cursor.moveToFirst()) cursor else null
        }
    }

    fun getRestExam(): Cursor? {
        return readableDatabase.use { db ->
            val sqlQuery = """
                SELECT * FROM ${ExamContract.TABLE_NAME}
                WHERE ${ExamContract.COLUMN_EXAM_NAME} NOT IN (
                    SELECT ${ExamContract.COLUMN_EXAM_NAME}
                    FROM ${ExamContract.TABLE_NAME}
                    ORDER BY ${ExamContract.COLUMN_START_TIME} ASC
                    LIMIT 1
                )
                ORDER BY ${ExamContract.COLUMN_START_TIME} ASC
            """.trimIndent()
            val cursor = db.rawQuery(sqlQuery, null)
            if (cursor.moveToFirst()) cursor else null
        }
    }

    fun hasExams(): Boolean {
        val db = this.readableDatabase
        var cursor: Cursor? = null
        return try {
            cursor = db.rawQuery("SELECT COUNT(*) FROM ${ExamContract.TABLE_NAME}", null)
            cursor.moveToFirst() && cursor.getInt(0) > 0
        } catch (e: Exception) {
            Log.e(TAG, "检查考试记录时出错: ${e.message}")
            false
        } finally {
            cursor?.close()
            db.close()
        }
    }

    fun getExamCount(): Int {
        val db = this.readableDatabase
        var cursor: Cursor? = null
        return try {
            cursor = db.rawQuery(
                "SELECT COUNT(*) FROM ${ExamContract.TABLE_NAME}",
                null
            )
            if (cursor.moveToFirst()) {
                cursor.getInt(0)
            } else {
                0
            }
        } catch (e: Exception) {
            Log.e(TAG, "获取考试数量时出错: ${e.message}")
            0
        } finally {
            cursor?.close()
            db.close()
        }
    }

    fun checkTimeConflict(examName: String, startTime: Long, duration: Int): Boolean {
        val db = this.readableDatabase
        val endTime = calculateEndTime(startTime, duration)
        var cursor: Cursor? = null
        return try {
            // 查询所有与给定时间区间有重叠且考试名称不同的考试
            cursor = db.rawQuery("""
            SELECT * FROM ${ExamContract.TABLE_NAME} 
            WHERE ${ExamContract.COLUMN_EXAM_NAME} != ? 
            AND NOT (${ExamContract.COLUMN_END_TIME} <= ? OR ${ExamContract.COLUMN_START_TIME} >= ?)
        """.trimIndent(), arrayOf(examName, startTime.toString(), endTime.toString()))
            cursor.count > 0
        } catch (e: Exception) {
            Log.e(TAG, "检查时间冲突时出错: ${e.message}")
            true
        } finally {
            cursor?.close()
            db.close()
        }
    }

    /* 工具函数 */
    private fun calculateEndTime(startTime: Long, duration: Int): Long {
        val startTimeStr = startTime.toString()
        val dateFormat = SimpleDateFormat("yyyyMMddHHmm", Locale.getDefault())
        return dateFormat.run {
            val startDate = parse(startTimeStr) ?: return@run 0L
            val endMillis = startDate.time + duration * 60 * 1000L
            val endDate = Date(endMillis)
            val endTimeStr = format(endDate)
            endTimeStr.toLongOrNull() ?: 0L
        }
    }

    private fun calculateReminderTime(startTime: Long, seconds: Int): Long {
        val sdf = SimpleDateFormat("yyyyMMddHHmm", Locale.getDefault())
        val date = sdf.parse(startTime.toString()) ?: return 0L
        val calendar = Calendar.getInstance().apply {
            time = date
            add(Calendar.SECOND, -seconds)
        }
        return sdf.format(calendar.time).toLong()
    }

    private fun sendExamChangeBroadcast() {
        val intent = Intent("com.ljj.examisto.ACTION_EXAM_CHANGE")
        Log.d(TAG, "发送了考试变化广播")
        LocalBroadcastManager.getInstance(context).sendBroadcast(intent)
    }

    private fun sendReminderChangeBroadcast() {
        val intent = Intent("com.ljj.examisto.ACTION_REMINDER_CHANGE")
        Log.d(TAG, "发送了提醒变化广播")
        LocalBroadcastManager.getInstance(context).sendBroadcast(intent)
    }
}

object ExamContract {
    const val TABLE_NAME = "exam"
    const val COLUMN_EXAM_NAME = "exam_name"
    const val COLUMN_LOCATION = "location"
    const val COLUMN_START_TIME = "start_time"
    const val COLUMN_DURATION = "duration"
    const val COLUMN_END_TIME = "end_time"
    const val COLUMN_REMARK = "remark"
    const val COLUMN_SET_TIME = "set_time"
    val SQL_CREATE_TABLE = """
        CREATE TABLE $TABLE_NAME (
            $COLUMN_EXAM_NAME TEXT PRIMARY KEY NOT NULL,
            $COLUMN_LOCATION TEXT,
            $COLUMN_START_TIME LONG NOT NULL,
            $COLUMN_DURATION INTEGER NOT NULL,
            $COLUMN_END_TIME LONG NOT NULL,
            $COLUMN_REMARK TEXT,
            $COLUMN_SET_TIME LONG NOT NULL
        )
    """.trimIndent()
}
object ReminderContract {
    const val TABLE_NAME = "reminder"
    private const val COLUMN_ID = "_id"
    const val COLUMN_EXAM_NAME = "exam_name"
    const val COLUMN_SECONDS = "seconds"
    const val COLUMN_REMINDER_TIME = "reminder_time"
    const val COLUMN_TYPE = "type"
    val SQL_CREATE_TABLE = """
        CREATE TABLE $TABLE_NAME (
            $COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT,
            $COLUMN_EXAM_NAME TEXT NOT NULL,
            $COLUMN_SECONDS INTEGER NOT NULL,
            $COLUMN_REMINDER_TIME LONG NOT NULL,
            $COLUMN_TYPE TEXT NOT NULL,
            FOREIGN KEY($COLUMN_EXAM_NAME) REFERENCES ${ExamContract.TABLE_NAME}(${ExamContract.COLUMN_EXAM_NAME}) ON DELETE CASCADE
        )
    """.trimIndent()
}